using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;

namespace EM.utility
{
    public class Notice
    {
        public enum TemplateTypes
        {
            NewUserNotification = 1,
            UserPasswordChangeNotification = 2,
            ExpenseNotification = 3
        }

        Conversion oC = new Conversion();

        public void SendUsingTemplate(TemplateTypes TemplateType, int UserID, string Password)
        {
            DataSrv dsrv = new DataSrv();
            DataTable dt = new DataTable();
            string sSQL = "";
            string EMAILSUBJECT, CONTENTDESC;
            string USERCODE, USERDESC, USEREMAIL;
            string UNIQUECODE = "";

            smtpSendMail sm = new smtpSendMail();

            if (!(TemplateType == TemplateTypes.NewUserNotification || TemplateType == TemplateTypes.UserPasswordChangeNotification))
            {
                throw new Exception("Invalid template.");
            }

            if (TemplateType == TemplateTypes.NewUserNotification)
            {
                UNIQUECODE = "new_user_notification";
            }
            else if (TemplateType == TemplateTypes.UserPasswordChangeNotification)
            {
                UNIQUECODE = "password_change_notification";
            }

            sSQL = "SELECT U.USERCODE, U.USERDESC, U.USEREMAIL";
            sSQL += " FROM TBLUSER U";
            sSQL += " WHERE U.USERID = " + UserID.ToString();
            dt = dsrv.CreateDataTable(sSQL, "");

            USERCODE = dt.Rows[0]["USERCODE"].ToString();
            USERDESC = dt.Rows[0]["USERDESC"].ToString();
            USEREMAIL = dt.Rows[0]["USEREMAIL"].ToString();
            //PREFERREDLANG = dt.Rows[0]["PREFERREDLANG"].ToString();

            sSQL = " SELECT EMAILSUBJECT, CONTENTDESC ";
            sSQL += " FROM TBLCONTENT_L L, TBLCONTENT C";
            sSQL += " WHERE L.CONTENTID = C.CONTENTID";
            sSQL += " AND UNIQUECODE =  '" + UNIQUECODE + "' ";
            sSQL += " AND LANGCODE = '" + HttpContext.Current.Session["LANGCODE"].ToString() + "'";
            dt = dsrv.CreateDataTable(sSQL, "");
            if (dt.Rows.Count > 0)
            {
                EMAILSUBJECT = dt.Rows[0]["EMAILSUBJECT"].ToString();
                CONTENTDESC = dt.Rows[0]["CONTENTDESC"].ToString();

                CONTENTDESC = CONTENTDESC.Replace("#password#", Password);
                CONTENTDESC = CONTENTDESC.Replace("#usercode#", USERCODE);
                CONTENTDESC = CONTENTDESC.Replace("#userdesc#", USERDESC);
                List<string> mTo = new List<string>();
                mTo.Add(USEREMAIL);
                sm.mTo = mTo;
                sm.mSubject = EMAILSUBJECT;
                sm.mMailBody = CONTENTDESC;
                sm.sendMailNow();
            }

        }

        public void ExpenseNotification(int expenseId)
        {
            DataSrv srv = new DataSrv();
            SqlCommand cmd = new SqlCommand();
            cmd = new SqlCommand();
            cmd.CommandText = @"
                                        SELECT U.USERDESC,U.USEREMAIL FROM TBLUSER U, TBLUSERROLE UR, TBLROLE R
                                        WHERE U.USERID = UR.USERID AND UR.ROLEID = R.ROLEID
                                        AND R.ROLENAME = 'system'
                                        
                                        SELECT ET.EXPENSETYPENAME, AMOUNT, INSTALLMENTCOUNT,LVL.LOOKUPNAME, CONVERT(NVARCHAR, INSTALLMENTSTARTDATE, 104) AS INSTALLMENTSTARTDATE,C.CURRENCYCODE,
                                        E.EXPENSEDATE,U.USERDESC, U.USEREMAIL  FROM TBLEXPENSE E, TBLEXPENSETYPE ET, TBLLOOKUPVALUE_L LVL , TBLUSER U, TBLCURRENCY C
                                        WHERE E.EXPENSETYPEID = ET.EXPENSETYPEID
                                        AND E.EXPENSEPAYMENTTYPE = LVL.LOOKUPVALUE
                                        AND LVL.LANGCODE = 'TR'
                                        AND LVL.LOOKUPTYPE = 'EXPENSE_PAYMENT_TYPE'
                                        AND U.USERID = E.EXPENSEUSERID
                                        AND C.CURRENCYID = E.CURRENCYID
                                        AND E.EXPENSEID = @EXPENSEID

                                        SELECT EMAILSUBJECT, CONTENTDESC 
                                        FROM TBLCONTENT_L L, TBLCONTENT C
                                        WHERE L.CONTENTID = C.CONTENTID
                                        AND UNIQUECODE =  'new_expense_notification'
                                        AND LANGCODE = 'TR' ";



            cmd.Parameters.AddWithValue("@EXPENSEID", expenseId);

            DataSet ds = srv.CreateDataSet(cmd);

            if (ds.Tables.Count > 0)
            {
                smtpSendMail sendMail = new smtpSendMail();
                List<string> mailAdd = new List<string>();

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    mailAdd.Add(ds.Tables[0].Rows[i]["USEREMAIL"].ToString());
                for (int i = 0; i < ds.Tables[1].Rows.Count; i++)
                    mailAdd.Add(ds.Tables[1].Rows[i]["USEREMAIL"].ToString());

                string EMAILSUBJECT = ds.Tables[2].Rows[0]["EMAILSUBJECT"].ToString();
                string CONTENTDESC = ds.Tables[2].Rows[0]["CONTENTDESC"].ToString();

                CONTENTDESC = CONTENTDESC.Replace("#usercode#", ds.Tables[1].Rows[0]["USERDESC"].ToString());
                CONTENTDESC = CONTENTDESC.Replace("#expensetype#", ds.Tables[1].Rows[0]["EXPENSETYPENAME"].ToString());
                CONTENTDESC = CONTENTDESC.Replace("#expensepaymenttype#", ds.Tables[1].Rows[0]["LOOKUPNAME"].ToString());
                CONTENTDESC = CONTENTDESC.Replace("#amount#", oC.CNumS2C(Convert.ToDouble(ds.Tables[1].Rows[0]["AMOUNT"])) + " " + ds.Tables[1].Rows[0]["CURRENCYCODE"].ToString());
                CONTENTDESC = CONTENTDESC.Replace("#installmentcount#", ds.Tables[1].Rows[0]["INSTALLMENTCOUNT"].ToString());
                CONTENTDESC = CONTENTDESC.Replace("#installmentstartdate#", ds.Tables[1].Rows[0]["INSTALLMENTSTARTDATE"].ToString());

                sendMail.mSubject = EMAILSUBJECT;
                sendMail.mMailBody = CONTENTDESC;
                sendMail.mTo = mailAdd;
                sendMail.sendMailNow();
            }
        }


        public void IncomeNotification(int incomeId)
        {
            DataSrv srv = new DataSrv();
            SqlCommand cmd = new SqlCommand();
            cmd = new SqlCommand();
            cmd.CommandText = @"
                                        SELECT U.USERDESC,U.USEREMAIL FROM TBLUSER U, TBLUSERROLE UR, TBLROLE R
                                WHERE U.USERID = UR.USERID AND UR.ROLEID = R.ROLEID
                                AND R.ROLENAME = 'system'

                                SELECT IT.INCOMETYPENAME, AMOUNT, CONVERT(NVARCHAR, I.INCOMEDATE,105) AS INCOMEDATE, C.CURRENCYCODE,
                                ,U.USERDESC, U.USEREMAIL  FROM TBLINCOME I, TBLINCOMETYPE IT,  TBLUSER U, TBLCURRENCY C
                                WHERE I.INCOMETYPEID= IT.INCOMETYPEID
                                AND U.USERID = I.INCOMEUSERID
                                AND I.INCOMEID = @INCOMEID
                                AND C.CURRENCYID = I.CURRENCYID

                                SELECT EMAILSUBJECT, CONTENTDESC 
                                FROM TBLCONTENT_L L, TBLCONTENT C
                                WHERE L.CONTENTID = C.CONTENTID
                                AND UNIQUECODE =  'new_income_notification'
                                AND LANGCODE = 'TR' ";



            cmd.Parameters.AddWithValue("@INCOMEID", incomeId);

            DataSet ds = srv.CreateDataSet(cmd);

            if (ds.Tables.Count > 0)
            {
                smtpSendMail sendMail = new smtpSendMail();
                List<string> mailAdd = new List<string>();

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    mailAdd.Add(ds.Tables[0].Rows[i]["USEREMAIL"].ToString());
                for (int i = 0; i < ds.Tables[1].Rows.Count; i++)
                    mailAdd.Add(ds.Tables[1].Rows[i]["USEREMAIL"].ToString());

                string EMAILSUBJECT = ds.Tables[2].Rows[0]["EMAILSUBJECT"].ToString();
                string CONTENTDESC = ds.Tables[2].Rows[0]["CONTENTDESC"].ToString();

                CONTENTDESC = CONTENTDESC.Replace("#usercode#", ds.Tables[1].Rows[0]["USERDESC"].ToString());
                CONTENTDESC = CONTENTDESC.Replace("#incometype#", ds.Tables[1].Rows[0]["INCOMETYPENAME"].ToString());
                CONTENTDESC = CONTENTDESC.Replace("#amount#", oC.CNumS2C(Convert.ToDouble(ds.Tables[1].Rows[0]["AMOUNT"])) + " " + ds.Tables[1].Rows[0]["CURRENCYCODE"].ToString());

                sendMail.mSubject = EMAILSUBJECT;
                sendMail.mMailBody = CONTENTDESC;
                sendMail.mTo = mailAdd;
                sendMail.sendMailNow();
            }
        }
    }
}

